import java.sql.*;
import java.util.LinkedList;
import java.util.List;

/**
 * 本类的getStatisticsData方法可以获取某一月的各项数据，并保存在本地数据库</p>
 * 在实际使用过程中，应当
 * 1.先调用本类的getRawData方法；
 * 2.人工插入、删除某些项目；
 * 3.使用GetStatisticsData类进行数据分析；★
 * 4.使用WriteReport类撰写统计月报。
 */
public class GetStatisticsData {

    /* 歌姬名称、引擎名称、团组名称 */
    private static final String[] VOCALS = {"洛天依", "言和", "乐正绫", "乐正龙牙", "徵羽摩柯", "墨清弦", "星尘", "海伊", "苍穹", "赤羽", "诗岸", "星尘Minus", "牧心", "艾可", "心华", "悦成", "章楚楚", "初音未来"};
    private static final String[] ENGINES = {"VOCALOID", "Synthesizer V"};
    private static final String[] GROUPS = {"Vsinger", "五维介质"};

    /* 数据表头、本月数据表 */
    private static final List<String> headers = new LinkedList<>();
    private static final List<StatisticsRecord> currentSheet = new LinkedList<>();

    public static void main(String[] args) {
        getStatisticsData(2022,1);
        showResult();
    }

    /**
     * 显示统计结果
     */
    public static void showResult() {
        System.out.println("类别\t投稿数\t过千数\t最大值\t总和");
        for (int i = 0; i < currentSheet.size(); i++) {
            String key = headers.get(i);
            StatisticsRecord record = currentSheet.get(i);
            System.out.println(key + "\t" + record.count + "\t" + record.over1000 + "\t" + record.max + "\t" + record.sum);
        }
    }

    /**
     * 获取本月的统计数据
     *
     * @param year  年份
     * @param month 月份
     */
    public static void getStatisticsData(int year, int month) {
        String monthString = GetRawData.monthToString(year, month);
        headers.clear();
        currentSheet.clear();
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection localConnection = DriverManager.
                    getConnection(LocalAccount.URL, LocalAccount.USER, LocalAccount.PASSWORD);
            for (int i = 0; i < VOCALS.length + ENGINES.length + GROUPS.length + 1; i++) {
                System.out.println("当前进度" + (i * 100 / (VOCALS.length + 5)) + "%");

                /* 获取filterString和key */
                String filterString;
                String key;
                if (i < VOCALS.length) {
                    filterString = "`name`='" + VOCALS[i] + "'";
                    key = VOCALS[i];
                } else if (i < VOCALS.length + ENGINES.length) {
                    filterString = "`engine`='" + ENGINES[i - VOCALS.length] + "'";
                    key = ENGINES[i - VOCALS.length];
                } else if (i < VOCALS.length + ENGINES.length + GROUPS.length) {
                    filterString = "`group`='" + GROUPS[i - VOCALS.length - ENGINES.length] + "'";
                    key = GROUPS[i - VOCALS.length - ENGINES.length];
                } else {
                    filterString = "1=1";
                    key = "总计";
                }


                String sql = "SELECT `view` FROM table_video,table_video_vocal,table_vocal WHERE `month` = '" + monthString + "' AND table_video.`aid`=table_video_vocal.`aid`\n" +
                        "AND table_vocal.`vid` = table_video_vocal.`vid` AND " + filterString + ";";
                Statement statement = localConnection.createStatement();
                statement.executeQuery(sql);
                ResultSet resultSet = statement.getResultSet();
                StatisticsRecord record = new StatisticsRecord();
                while (resultSet.next()) {
                    record.add(resultSet.getInt("view"));
                }

                headers.add(key);
                currentSheet.add(record);
                record.insertIntoDB(localConnection, monthString, key);

                resultSet.close();
                statement.close();
            }
            localConnection.close();
        } catch (SQLException | ClassNotFoundException exception) {
            exception.printStackTrace();
        }
    }
}
